set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode = 200;
set hive.exec.max.dynamic.partitions = 200;
-- **************************************************************************
--    Project Name:   高投诉驿站统计-汇总
--    Description :   统计投诉量大于1票及以上的驿站个数
--    Author :        郭瑞玲
--    date：          2022/07/10
-- **************************************************************************
--    Relation ：
--    jms_dm.dm_end_dispatch_saven_zero_out_cnt_dt << [
--     jms_dm.dm_end_dispatch_complain_deital
--     jms_dm.dm_network_end_dispatch_diff_detail
--     jms_dim.dim_network_whole_massage
--    ]
-- **************************************************************************
--    modify by  guoruiling添加品牌字段 20220914
-- **************************************************************************

with tab_complain as( --门店投诉量
select   --网点、门店、投诉量汇总
update_time,
store_code,
max(store_name) store_name,
scansitecode,
max(inputsite) inputsite,
count(distinct billcode) complain_bill_cnt, --投诉量
business_id,
max(business_name) business_name
from jms_dm.dm_end_dispatch_complain_deital
where dt between date_add('{{ execution_date | cst_ds }}',-6) and '{{ execution_date | cst_ds }}'
and scansitecode is not null
group by
store_code,
scansitecode,
update_time,
business_id 
),
tab_store_in as( --门店入库量
select store_code,scansitecode,sum(store_in_total) store_in_total,update_time,business_id
from jms_dm.dm_network_end_dispatch_diff_rate
where dt between date_add('{{ execution_date | cst_ds }}',-6) and '{{ execution_date | cst_ds }}'
group by store_code,scansitecode,update_time,business_id
)
insert overwrite table jms_dm.dm_end_dispatch_complain_high_cnt_dt  partition(dt)
select
t1.update_time,
net.manage_code,
net.manage_name ,
net.fran_code ,
net.fran_name ,
net.agent_code ,
net.agent_name ,
t1.store_code,
t1.store_name,
t1.scansitecode,
t1.inputsite,
t1.complain_bill_cnt,
t1.store_in_total,
t1.store_cnt_gre1,
t1.store_cnt_gre1_in,
t1.store_cnt_gre1_complain,
t1.store_cnt_gre2,
t1.store_cnt_gre2_in,
t1.store_cnt_gre2_complain,
sum(if(datediff(t1.update_time,t1.next_next_date)=2,1,0)) over(partition by t1.update_time,t1.scansitecode )  three_days_store, --连续3天投诉驿站数
if(datediff(t1.update_time,t1.next_next_date)=2,1,0) is_three_days, --是否连续三天
t1.business_id ,
t1.business_name,
t1.update_time as dt
from
(
select
t.update_time,      --日期
t.store_code,        --店铺编码
t.store_name,
t.scansitecode,       --网点
t.inputsite,
t.complain_bill_cnt,   --门店对应的投诉量
store_in.store_in_total,--门店对应的入库量
count(if(t.complain_bill_cnt>1,t.store_code,null)) over(partition by t.update_time,t.scansitecode ) store_cnt_gre1,  --投诉量大于1的驿站数
sum(if(t.complain_bill_cnt>1,store_in.store_in_total,0)) over(partition by t.update_time,t.scansitecode ) store_cnt_gre1_in, --投诉量大于1的入库量
sum(if(t.complain_bill_cnt>1,t.complain_bill_cnt,0)) over(partition by t.update_time,t.scansitecode ) store_cnt_gre1_complain, --投诉量大于1的投诉量
count(if(complain_bill_cnt>2,t.store_code,null)) over(partition by t.update_time,t.scansitecode ) store_cnt_gre2,  --投诉量大于2的驿站数
sum(if(t.complain_bill_cnt>2,store_in.store_in_total,0)) over(partition by t.update_time,t.scansitecode ) store_cnt_gre2_in, --投诉量大于2的入库量
sum(if(t.complain_bill_cnt>2,t.complain_bill_cnt,0)) over(partition by t.update_time,t.scansitecode ) store_cnt_gre2_complain, --投诉量大于2的投诉量
lead(t.update_time,2,null) over(partition by t.scansitecode,t.store_code order by t.update_time desc)  as next_next_date --判断是都连续3日
,t.business_id 
,t.business_name
from
tab_complain  t  --门店投诉汇总
inner join
tab_store_in store_in --门店入库量
on store_in.store_code=t.store_code
and store_in.scansitecode=t.scansitecode
and store_in.update_time=t.update_time
and store_in.business_id =t.business_id 
)t1 left join jms_dim.dim_network_whole_massage net on net.code=t1.scansitecode
where t1.complain_bill_cnt>1
 distribute by pmod(hash(rand()), 3);